﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;
using VideoClub.Entities;

namespace VideoClub.DataAccess
{
    internal class ActorDAO
    {

        #region ObtenerTodosLosActores
        public static List<Actor> ObtenerTodosLosActores()
        {
            List<Actor> list = new List<Actor>();

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"SELECT IdActor ,Nombre ,Apellido FROM actores ORDER BY Nombre";
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    list.Add(LoadActor(reader));
                }
            }

            return list;
        }

        #endregion

        #region ObtenerActorPorId

        public static Actor ObtenerActorPorId(int id)
        {
            Actor actor = null;

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"SELECT IdActor,Nombre,Apellido FROM actores WHERE IdActor = @actorId";

                var cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("actorId", id);

                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    actor = LoadActor(reader);
                }
                conn.Close();
            }

            return actor;

        }
        #endregion

        #region EliminarActor
        public static Boolean Eliminar (int id)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
                {
                    conn.Open();
                    string sql = @" DELETE FROM actores WHERE IdActor = @idActor ";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("idActor", id);
                    cmd.ExecuteScalar();
                    conn.Close();
                }
                return true;
            }
            catch (Exception)
            {
                return false;
                throw;
            }
            
        }

        #endregion

        #region ValidarExistencia
        public static bool ValidarExistencia(String nombre, String apellido)
        {
            int nrorecord = 0;

            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"SELECT Count(*) FROM actores WHERE Nombre = @nombre AND Apellido = @apellido";

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("nombre", nombre);
                cmd.Parameters.AddWithValue("apellido", apellido);
                nrorecord = Convert.ToInt32(cmd.ExecuteScalar());
                conn.Close();
            }

            return nrorecord > 0;
        }
        #endregion

        #region InsertarActor

        public static Actor Insertar(Actor actor)
        {
            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();
                string sql = @"INSERT INTO actores (Nombre, Apellido) 
                                    VALUES (@Nombre, @Apellido)
                               SELECT SCOPE_IDENTITY()";

                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@Nombre", actor.pnombre);
                cmd.Parameters.AddWithValue("@Apellido", actor.papellido);
                actor.pidActor = Convert.ToInt32(cmd.ExecuteScalar());
                conn.Close();
            }

            return actor;
        }
        #endregion

        #region ModificarActor

        public static Actor Actualizar(Actor actor)
        {
            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"UPDATE Actores SET  
                                            Nombre = @Nombre, 
                                            Apellido = @Apellido 
                                    WHERE IdActor = @idActor";

                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("@Nombre", actor.pnombre);
                cmd.Parameters.AddWithValue("@Apellido", actor.papellido);
                cmd.Parameters.AddWithValue("@idActor", actor.pidActor);
                cmd.ExecuteNonQuery();

            }

            return actor;
        }

        #endregion

        #region LoadActor

        private static Actor LoadActor(IDataReader reader)
        {
            try
            {
                Actor actor = new Actor();

                actor.pidActor = Convert.ToInt32(reader["IdActor"]);
                actor.papellido = Convert.ToString(reader["Apellido"]);
                actor.pnombre = Convert.ToString(reader["Nombre"]);

                return actor;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message.ToString());
                
                throw;
            }
            
        }
        #endregion

        public static List<Actor> ObtenerActorPorFiltro(string filtro)
        {
            List<Actor> list = new List<Actor>();
            using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=VideoClub;Integrated Security=True"))
            {
                conn.Open();

                string sql = @"SELECT IdActor ,Nombre ,Apellido  FROM actores WHERE Apellido Like '%" + filtro + "%'";

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("apellido", filtro);
                SqlDataReader reader = cmd.ExecuteReader();

                
                while (reader.Read())
                {
                    list.Add(LoadActor(reader));
                }
                conn.Close();
            }
            return list;
        }
    }
}

